package com.example.myapplication.utils;

import android.annotation.SuppressLint;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.myapplication.App;
import com.example.myapplication.R;
import com.example.myapplication.model.Word;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class SQLHelper {

    private SQLiteDatabase mDataBase = null;
    private static SQLHelper mInstance;

    private SQLHelper() {}

    public static SQLHelper getInstance() {
        if(mInstance == null)
            mInstance = new SQLHelper();
        return mInstance;
    }

    private void openDatabase() {
        if(mDataBase != null) {
            if(mDataBase.isOpen())
                return;
        }
        //把raw路径中的文件 写入 文件系统中
        InputStream is = App.AppContext.getResources().openRawResource(R.raw.words);
        String path = App.AppContext.getExternalFilesDir("").getAbsolutePath() + File.separator + "words.db";
        if(!new File(path).exists()) {
            try {
                FileOutputStream os = new FileOutputStream(path);
                byte[] buf = new byte[is.available()];
                while (true) {
                    int len = is.read(buf);
                    if (len > 0) {
                        os.write(buf, 0, len);
                    } else {
                        break;
                    }
                }
                os.flush();
                os.close();
                is.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        mDataBase = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
    }

    @SuppressLint("Range")
    public List<Word> query(String table, int start, int end) {
        String sql = "select * from "+table+" where id >= "+start+" and id <= "+end;
        openDatabase();
        Cursor cursor = mDataBase.rawQuery(sql, null);
        List<Word> result = new ArrayList<>();
        while (cursor.moveToNext()) {
            Word word = new Word();
            word.id = cursor.getInt(0);
            word.en = cursor.getString(cursor.getColumnIndex("en"));
            word.vt = cursor.getString(2);
            word.vi = cursor.getString(3);
            word.n = cursor.getString(4);
            word.adj = cursor.getString(5);
            word.adv = cursor.getString(6);
            word.conj = cursor.getString(7);
            word.prep = cursor.getString(8);
            word.pron = cursor.getString(9);
            word.art = cursor.getString(10);
            word.abbr = cursor.getString(11);
            word.other = cursor.getString(12);
            result.add(word);
        }
        cursor.close();
        return result;
    }

    public void close() {
        if(mDataBase != null)
            mDataBase.close();
    }
}
